/**
 * Created by yan_my on 2017/1/14.
 */
import java.sql.*;
import java.util.ArrayList;

public class DataBase {
    public static void main(String[] args) throws Exception {
        try {
            //调用Class.forName()方法加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/homework_5?useSSL=true";
            String name = "root";
            String password = "23336666";

            Connection con = DriverManager.getConnection(url, name, password);
            con.close();
        } catch (SQLException s) {
            s.printStackTrace();
        }
    }
    public void insert(int av_number,String title,String author,String tname,int favorite){
        try{
            String url = "jdbc:mysql://localhost:3306/homework_5?useSSL=true";
            String name = "root";
            String password = "23336666";
            Connection con = DriverManager.getConnection(url, name, password);

            String sql = "INSERT INTO homework_5_1 (av_number,title,author,tname,favorite) VALUES (?,?,?,?,?)";
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setInt(1, av_number);
            pre.setString(2, title);
            pre.setString(3, author);
            pre.setString(4,tname);
            pre.setInt(5, favorite);

            pre.addBatch();
            pre.executeUpdate();

            con.close();
            pre.close();

        }catch(SQLException s){
            s.printStackTrace();
        }
    }
    public static ArrayList getVideo(String website) throws Exception
    {
        String category = Test.getCategory(website);
        ArrayList video = new ArrayList();

        String url = "jdbc:mysql://localhost:3306/homework_5?useSSL=true";
        String name = "root";
        String password = "23336666";
        Connection con = DriverManager.getConnection(url, name, password);

        String sql = "SELECT * FROM homework_5_1 WHERE (tname='"+category+"') ORDER BY favorites desc limit 3";
        PreparedStatement pre = con.prepareStatement(sql);
        ResultSet rs = pre.executeQuery(sql);
        while (rs.next()) {
            video.add("http://www.ibilibili.com/video/av" + rs.getString(1) + "/");
        }
        return video;

    }
}

